"""
Database adapters for timeseries data storage

Provides unified interface for different database backends:
- SQLite (development)
- TimescaleDB (production - future)
"""

from __future__ import annotations

from datetime import datetime
from typing import Any, Protocol

from sqlalchemy import text
from sqlalchemy.ext.asyncio import AsyncSession


class TimeseriesAdapter(Protocol):
    """Protocol for timeseries database adapters"""

    async def save_metric(
        self,
        db: AsyncSession,
        device_id: int,
        metric_name: str,
        value: float,
        timestamp: datetime,
        quality: str = "good",
        unit: str | None = None,
    ) -> bool:
        """Save a single metric to database"""
        ...

    async def query_metrics(
        self,
        db: AsyncSession,
        device_id: int,
        metric_name: str,
        start_time: datetime,
        end_time: datetime,
        limit: int = 1000,
    ) -> list[dict[str, Any]]:
        """Query metrics within time range"""
        ...


class SQLiteTimeseriesAdapter:
    """
    SQLite adapter for timeseries data

    Stores metrics in a simple table structure for development/testing.
    Not optimized for production-scale timeseries workloads.
    """

    async def save_metric(
        self,
        db: AsyncSession,
        device_id: int,
        metric_name: str,
        value: float,
        timestamp: datetime,
        quality: str = "good",
        unit: str | None = None,
    ) -> bool:
        """
        Save a metric to SQLite database

        Args:
            db: Database session
            device_id: Device ID
            metric_name: Metric name
            value: Metric value
            timestamp: Timestamp
            quality: Data quality (good/bad/uncertain)
            unit: Unit of measurement

        Returns:
            bool: True if save successful
        """
        try:
            # Create table if not exists
            await db.execute(
                text(
                    """
                    CREATE TABLE IF NOT EXISTS device_metrics (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        device_id INTEGER NOT NULL,
                        metric_name TEXT NOT NULL,
                        value REAL,
                        timestamp TIMESTAMP NOT NULL,
                        quality TEXT DEFAULT 'good',
                        unit TEXT,
                        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                    )
                    """
                )
            )

            # Insert metric
            await db.execute(
                text(
                    """
                    INSERT INTO device_metrics (device_id, metric_name, value, timestamp, quality, unit)
                    VALUES (:device_id, :metric_name, :value, :timestamp, :quality, :unit)
                    """
                ),
                {
                    "device_id": device_id,
                    "metric_name": metric_name,
                    "value": value,
                    "timestamp": timestamp,
                    "quality": quality,
                    "unit": unit,
                },
            )

            await db.commit()
            return True

        except Exception as e:
            await db.rollback()
            raise RuntimeError(f"Failed to save metric: {e}")

    async def query_metrics(
        self,
        db: AsyncSession,
        device_id: int,
        metric_name: str,
        start_time: datetime,
        end_time: datetime,
        limit: int = 1000,
    ) -> list[dict[str, Any]]:
        """
        Query metrics from SQLite database

        Args:
            db: Database session
            device_id: Device ID
            metric_name: Metric name
            start_time: Start time
            end_time: End time
            limit: Max number of results

        Returns:
            List of metric dictionaries
        """
        # Ensure table exists
        await db.execute(
            text(
                """
                CREATE TABLE IF NOT EXISTS device_metrics (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    device_id INTEGER NOT NULL,
                    metric_name TEXT NOT NULL,
                    value REAL,
                    timestamp TIMESTAMP NOT NULL,
                    quality TEXT DEFAULT 'good',
                    unit TEXT,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
                """
            )
        )

        result = await db.execute(
            text(
                """
                SELECT device_id, metric_name, value, timestamp, quality, unit
                FROM device_metrics
                WHERE device_id = :device_id
                  AND metric_name = :metric_name
                  AND timestamp >= :start_time
                  AND timestamp <= :end_time
                ORDER BY timestamp DESC
                LIMIT :limit
                """
            ),
            {
                "device_id": device_id,
                "metric_name": metric_name,
                "start_time": start_time,
                "end_time": end_time,
                "limit": limit,
            },
        )

        rows = result.fetchall()
        return [
            {
                "device_id": row[0],
                "metric_name": row[1],
                "value": row[2],
                "timestamp": row[3],
                "quality": row[4],
                "unit": row[5],
            }
            for row in rows
        ]


def get_timeseries_adapter() -> TimeseriesAdapter:
    """
    Get appropriate timeseries adapter based on configuration

    Returns:
        TimeseriesAdapter instance
    """
    # For now, always return SQLite adapter
    # Future: Check config.TIMESERIES_TYPE and return appropriate adapter
    return SQLiteTimeseriesAdapter()
